/**
 * Copyright [2010] Delta Air Lines, Inc. All Rights Reserved Access,
 * Modification, or Use Prohibited Without Express Permission of Delta Air
 * Lines.
 */
package com.delta.rafg.core.database.impl;


import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.delta.rafg.core.config.ConfigSettings;
import com.delta.rafg.core.database.DataHandlerOptions;
import com.delta.rafg.core.database.impl.util.DatabaseUtils;
import com.delta.rafg.core.exception.RAFGException;
import com.delta.rafg.core.log.RAFGLogger;


/**
 * @author RAFG Development Team (D9127520)
 */
public class JavaDBCacheDataHandlerImpl extends ACacheDataHandler {

    /**
     * Holds the value of serialVersionUID property.
     */
    private static final long serialVersionUID = 7188001566021492229L;

    /**
     * Holds the value of driverClassName property.
     */
    private final String driverClassName =
        "org.apache.derby.jdbc.EmbeddedDriver";

    /**
     * Holds the value of protocol property.
     */
    private String protocol = "jdbc:derby:";

    private static final int SCHEMA_MISSING_ERR = 30000;

    /**
     * Holds the value of dataBasePath property.
     */
    private String dataBasePath;

    /**
     * Initialize the cache data handler.
     * @param options - DataHandlerOptions for initializing CacheDataHandler
     *            <p>
     *            <b>Optional DataHandlerOptions custom mappings</b> <br>
     *            "DataBasePath" - Override default Derby DataBase Path location
     * @throws RAFGException
     */
    @Override
    public void initialize(DataHandlerOptions options) throws RAFGException {
        super.initialize(options);
        ConfigSettings configSettings = options.getConfigSettings();
        String overideDataBasePath = null;
        if (options.getDriverClassName() == null) {
            options.setDriverClassName(driverClassName);
        }
        if (options.getProtocol() != null) {
        	protocol = options.getProtocol();
        }
        try {
            if (options.hasDataHandlerSpecificOptionsMap()) {
                overideDataBasePath =
                    (String) options.getDataHandlerSpecificOptionsMap().get(
                        "DataBasePath");
            }
            if (overideDataBasePath != null) {
                dataBasePath = overideDataBasePath;
            } else {
                final File sysTempDir =
                    new File(System.getProperty("java.io.tmpdir"));
                final File dbNameFile =
                    new File(sysTempDir, "rafg/javadb_cache.db");
                dataBasePath = dbNameFile.getCanonicalPath();
            }
        } catch (final IOException e) {
            throw new RAFGException(e);
        }
        
        //if config set to remove the javadb storage on initialization. 
        if(Boolean.parseBoolean(configSettings.getProperty(getDataHandlerPropertyPrefix() + ".CleanOnStart", "false"))) {
        	removeJavaDBStorage();
        }
        
        options.setConnectionUrl(protocol + dataBasePath + ";create=true");
//        AxisLogger.debug("CacheDataHandler: ConnectionUrl=" + options.getConnectionUrl());
    }

    /**
     * This method is used to get a database connection and initialize sqlConnection
     * variable.  This implementation will try to open the connection, if it fails,
     * the JavaDB database location will try to be removed then it will try again.
     * @return Connection
     * @throws RAFGException
     */
    @Override
    public synchronized Connection getConnection() throws RAFGException {

        try {
            Connection conn;
            if (getDataSource() == null) {
                if (sqlConnection == null || sqlConnection.isClosed()) {
                	sqlConnection = openConnection();
                }
                conn = sqlConnection;
            } else {
                conn = getDataSource().getConnection();
            }
            return conn;
        } catch (final java.sql.SQLException e) {
        	//will try again below, do nothing this time.
        } catch (final ClassNotFoundException e) {
            throw new RAFGException(e);
        }
        
        //If I reach here, it is because it failed to open for some reason.
        //Remove the storage path and open it again.
        try {
        	removeJavaDBStorage();
        	sqlConnection = openConnection();
        	return sqlConnection;
        } catch (final java.sql.SQLException e) {
            throw new RAFGException(e);
        } catch (final ClassNotFoundException e) {
            throw new RAFGException(e);
        }
        
    }
    
    /**
     * Return a NEW connection to the database.  If this is used, this connection must be closed when done.
     * @return new database connection
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection openConnection() throws ClassNotFoundException, SQLException {
    	Connection conn;
    	
        final String driverClass = getDataHandlerOptions().getDriverClassName();
		Class.forName(driverClass);
        final String url = getDataHandlerOptions().getConnectionUrl();
        final String user = getDataHandlerOptions().getUsername();
        final String pass = getDataHandlerOptions().getPassword();
        conn  = DriverManager.getConnection(url, user, pass);
        return conn;
    }

    private void removeJavaDBStorage() throws RAFGException {
    	//make sure the length of the database path is greater that 3 characters.
    	//this should prevent paths like /, ./, c:/, etc from accidently getting passed.
    	if (dataBasePath != null && dataBasePath.length() > 3) {
    		//Inspect directory to make sure it looks like a JavaDB storage directory.
    		File javaDbDir = new File(dataBasePath);
    		if (javaDbDir.isDirectory()) {
    			String[] contents = javaDbDir.list();
    			for (String f : contents) {
    				if (!f.equals("log") && !f.equals("seg0") && !f.equals("service.properties")
    						&& !f.equals("tmp") && !f.equals("jar") && !f.equals("db.lck") && !f.equals("dbex.lck")) {
    					throw new RAFGException("Detected non-JavaDB storage file " + f + " in JavaDB path " 
    							+ dataBasePath + " while attempting to delete JavaDB storage.  Deletion Aborted. The JavaDB Cache most likely is not working right now.");
    				}
    			}
    		}
    		
    		//If I made it this far, the database path has been verified.  If the javaDbDir is a file, this will 
    		//remove that too.
    		deleteDir(javaDbDir);
    	}
    }
    
    private boolean deleteDir(File dir) {
        if (dir.isDirectory()) {
            String[] children = dir.list();
            for (int i=0; i<children.length; i++) {
                boolean success = deleteDir(new File(dir, children[i]));
                if (!success) {
                    return false;
                }
            }
        }
        return dir.delete();
    }
    
    /**
     * This method initialize DataHandlerOptions
     * @param dataHandlerPropertyIdentifier
     * @throws RAFGException when input parameter is null
     */
    @Override
    public void initialize(final String dataHandlerPropertyIdentifier)
        throws RAFGException {

        if (dataHandlerPropertyIdentifier == null) { throw new java.lang.IllegalArgumentException(
            "dataHandlerPropertyIdentifier cannot be null"); }
        ConfigSettings configSettings = ConfigSettings.getSingleton();
        dataHandlerOptions = new DataHandlerOptions();
        dataHandlerOptions.setConfigSettings(configSettings);
        dataHandlerOptions
            .setDataHandlerPropertyIdentifier(dataHandlerPropertyIdentifier);
        
        initialize(dataHandlerOptions);
    }
    
    @Override
    public void destroy() {
    	try {
    		if (sqlConnection != null) {
    			sqlConnection.close();
            	sqlConnection = null;
    		}
    		String connStr = getDataHandlerOptions().getConnectionUrl();
    		connStr += ";shutdown=true";
			DriverManager.getConnection(connStr);
    	} catch (java.sql.SQLNonTransientConnectionException e) {
			//This error code means the close was successfull.
			if (!e.getSQLState().equals("08006") && !e.getSQLState().equals("25000")) {
//				RAFGLogger.error(e, "Failed to close JavaDB database: " + e.getMessage());
			}
		} catch (SQLException e) {
//			RAFGLogger.error(e, "Failed to close JavaDB database: " + e.getMessage());
		}
    	sqlConnection = null;
    	super.destroy();
    }

	@Override
	public boolean isSchemaExistsException(SQLException e) {
		int err = e.getErrorCode();
	    if (err == SCHEMA_MISSING_ERR) {
	    	return true;
	    } else {
	    	return false;
	    }
	}

	@Override
	public boolean checkTableExists(Statement stmt, String fullyQualifiedTableName) throws RAFGException {
		boolean tableExists = true;
		String table = DatabaseUtils.getTableName(fullyQualifiedTableName);
		java.sql.ResultSet rs = null;
		try {
	        //this only works on JavaDB to determine if the table exists. 
	        String cmd = "select tablename from sys.systables where tablename = '" + table.toUpperCase() + "'";
	        rs = stmt.executeQuery(cmd);
	    	if(!rs.next()) { //if no results are returned at all, the table does not exist.
	    		tableExists = false;
	   		}
		} catch (SQLException e) {
			//if its because the table doesn't exist, return that the cache has not been created yet.
			if (isSchemaExistsException(e)) {
				tableExists = false;
			}
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					throw new RAFGException(e);
				}
			}
		}
		return tableExists;
	}

	@Override
	public void createCacheTable(String fullyQualifiedTableName, List<CacheTableColumnInfo> columns) throws RAFGException {
		
//		String schemaName = DatabaseUtils.getSchemaName(cacheTable);
//		String tableName = DatabaseUtils.getTableName(cacheTable);
//		String fullyQualifiedTableName = schemaName + "." + tableName;
		
		String insertCmd = getCreateTableString(fullyQualifiedTableName, columns);
		
		RAFGLogger.info("Creating Cache Table " + fullyQualifiedTableName + ": " + insertCmd);
		
		Connection sqlConn = getConnection();
		try {
			Statement stmt = sqlConn.createStatement();
			stmt.execute(insertCmd);
			stmt.close();
		} catch (SQLException e) {
			throw new RAFGException("Could not create cache table " + fullyQualifiedTableName, e);
		}
	}
	
    protected String getCreateTableString(String fullyQualifiedTableName, List<CacheTableColumnInfo> columns) {

    	int nbrNewlines = 0;
    	boolean bHasPrimaryKeys = false;
    	
        StringBuffer sqlCmdSb = new StringBuffer();
        sqlCmdSb.append("CREATE TABLE ");
        sqlCmdSb.append(fullyQualifiedTableName);
        sqlCmdSb.append(" (");

        for (int i = 0; i < columns.size(); i++) {
        	CacheTableColumnInfo colInfo = columns.get(i);
//            String name = meta.getColumnName(i);
            sqlCmdSb.append(colInfo.getColumnName() + " ");
            int iType = colInfo.getColumnType();
            String type = DatabaseUtils.getJdbcTypeName(iType);
            int size = colInfo.getColumnLen();
            int scale = colInfo.getColumnScale();
            boolean nullable = colInfo.isNullable();
            
            if (colInfo.isPrimaryKey()) {
            	bHasPrimaryKeys = true;
            }
/*
            if (iType == java.sql.Types.VARCHAR) {
                type = "VARCHAR";
            } else if (iType == .equalsIgnoreCase("NUMBER")) {
                type = "NUMERIC";
            } else if (type.equalsIgnoreCase("LONG")) {
                type = "CLOB";
            } else {
            	type = "VARCHAR";
            }
*/
            
            sqlCmdSb.append(type);
            if (size > 0 && !type.contains("LOB") && !type.contains("DATE")) {
                if (scale <= 0) {
                    sqlCmdSb.append("(" + size + ")");
                } else {
                    sqlCmdSb.append("(" + size + ", " + scale + ")");
                }

            }

            if (!nullable) {
                sqlCmdSb.append(" NOT NULL");
            }

            if (i < columns.size()-1) {
                sqlCmdSb.append(",");
            }

            //check if I need to add a newline - only if the line has grown more than 512 bytes.  There is a line limit...
            //since ";" terminates the command, you can split lines up...
            long currNbrNewLinesNeeded = sqlCmdSb.length() / 512;
            if (currNbrNewLinesNeeded > nbrNewlines) {
                nbrNewlines++;
                sqlCmdSb.append("\n");
            }
        }

        boolean bFirst = true;
        if (bHasPrimaryKeys) {
            sqlCmdSb.append(", PRIMARY KEY (");
            for (CacheTableColumnInfo colInfo : columns) {
            	if(colInfo.isPrimaryKey()) {
	                if (!bFirst) {
	                    sqlCmdSb.append(", ");
	                } else {
	                    bFirst = false;
	                }
	                sqlCmdSb.append(colInfo.getColumnName());
            	}
            }
            sqlCmdSb.append(")");
        }

        sqlCmdSb.append(")");

        return sqlCmdSb.toString();
    }
}
